/*
 * @Author: xiaodi 53527718@qq.com
 * @Date: 2025-10-06 21:29:13
 * @LastEditors: xiaodi 53527718@qq.com
 * @LastEditTime: 2025-10-27 21:58:21
 * @FilePath: \new-pc-cashier\electron\cxtmssql\database.ts
 * @Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 */
/*
 * @Description: CXT SQL Server 数据库操作层
 * @Author: duan_shuangjun 丶
 * @Date: 2025-09-24 22:00:00
 */
// 使用动态导入来避免 ES 模块问题
import * as mssql from 'mssql'
import { writeLog } from '../ipc/wsLogger'
// 数据库配置
const config = {
    server: '127.0.0.1',
    // server: '192.168.80.27', // 调试库地址
    database: 'CXT_NaiMa',
    user: 'sa',
    password: '85863015',
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    },
    options: {
        encrypt: false,
        enableArithAbort: true
    }
}

// 连接池
let pool: mssql.ConnectionPool | null = null

/**
 * 记录日志的辅助函数
 */
function logError(level: string, category: string, message: string, error?: any): void {
    const timestamp = new Date().toISOString()
    let logLine = `${timestamp} | ${level} | ${category} | ${message}`
    
    if (error) {
        const errorMsg = error instanceof Error ? error.message : String(error)
        logLine += ` | DATA:${JSON.stringify({ error: errorMsg })}`
    }
    
    writeLog(logLine).catch(err => {
        console.error('写入日志失败:', err)
    })
}

/**
 * 初始化数据库连接池
 */
export async function initCXTDatabase() {
    try {
        pool = new mssql.ConnectionPool(config)
        await pool.connect()
        return pool
    } catch (error) {
        logError('ERROR', 'CXT-DB', '数据库连接失败', error)
        throw error
    }
}

/**
 * 获取连接池
 */
export function getCXTPool(): mssql.ConnectionPool {
    if (!pool) {
        throw new Error('CXT 数据库未初始化，请先调用 initCXTDatabase()')
    }
    return pool
}

/**
 * 关闭数据库连接
 */
export async function closeCXTDatabase() {
    if (pool) {
        await pool.close()
        pool = null
    }
}

/**
 * 检查 CXT 数据库连接状态
 */
export function isCXTDatabaseConnected(): boolean {
    return pool !== null && pool.connected
}

/**
 * CXT 数据库操作类
 */
export class CXTDatabase {
    private pool: mssql.ConnectionPool | null = null

    constructor() {
        // 延迟初始化，不在构造函数中获取连接池
    }

    private getPool(): mssql.ConnectionPool {
        if (!this.pool) {
            if (!pool) {
                throw new Error('CXT 数据库未初始化，请先调用 initCXTDatabase()')
            }
            this.pool = pool
        }
        return this.pool
    }

    /**
     * 格式化SQL值
     */
    private formatSqlValue(value: any): string {
        if (value === null || value === undefined) {
            return 'NULL'
        } else if (typeof value === 'number') {
            return value.toString()
        } else if (typeof value === 'boolean') {
            return value ? '1' : '0'
        } else if (value instanceof Date) {
            return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`
        } else {
            // 转义单引号
            return `'${String(value).replace(/'/g, "''")}'`
        }
    }

    /**
     * 查询单条记录
     */
    async one(table: string, where: Record<string, any> = {}, field: string[] = []): Promise<any> {
        try {
            let queryField = '*'
            if (field.length > 0) {
                queryField = field.join(',')
            }

            let whereClause = ''
            for (const [key, value] of Object.entries(where)) {
                if (key && value !== undefined && value !== null) {
                    whereClause += ` ${key} = ${this.formatSqlValue(value)} AND`
                }
            }

            if (whereClause) {
                whereClause = 'WHERE' + whereClause.replace(/AND$/, '')
            }

            const sql = `SELECT TOP 1 ${queryField} FROM ${table} ${whereClause}`
            console.log('查询one ---SQL:>>', sql)

            const request = this.getPool().request()
            const result = await request.query(sql)
            return result.recordset[0] || null
        } catch (error) {
            logError('ERROR', 'CXT-DB', '查询单条记录失败', { table, where, error })
            throw error
        }
    }

    /**
     * 查询列表
     */
    async list(table: string, where: Record<string, any> = {}, field: string[] = []): Promise<any[]> {
        try {
            let queryField = '*'
            if (field.length > 0) {
                queryField = field.join(',')
            }

            let whereClause = ''
            for (const whereKey in where) {
                if (Object.prototype.toString.call(where[whereKey]) === '[object Object]') {
                  if (where[whereKey].type === 'in') {
                    whereClause += ` ${whereKey} ${where[whereKey].type} ${where[whereKey].value} AND`
                  } else {
                    whereClause += ` ${whereKey} ${where[whereKey].type} '${where[whereKey].value}' AND`
                  }
                } else {
                  whereClause += ` ${whereKey}='${where[whereKey]}' AND`
                }
              }

            if (whereClause) {
                whereClause = 'WHERE' + whereClause.replace(/AND$/, '')
            }

            const sql = `SELECT ${queryField} FROM ${table} ${whereClause}`
            console.log('list SQL:>>', sql)

            const request = this.getPool().request()
            const result = await request.query(sql)
            return result.recordset
        } catch (error) {
            logError('ERROR', 'CXT-DB', '查询列表失败', { table, where, error })
            throw error
        }
    }

    /**
     * 插入数据
     */
    async insert(table: string, data: Record<string, any>): Promise<any> {
        try {
            const columns = Object.keys(data)
            const values = columns.map(col => this.formatSqlValue(data[col]))

            const sql = `INSERT INTO ${table} (${columns.join(',')}) VALUES (${values.join(',')})`
            console.log('插入数据-- SQL:>>', sql)

            const request = this.getPool().request()
            const result = await request.query(sql)
            return result
        } catch (error) {
            logError('ERROR', 'CXT-DB', '插入数据失败', { table, data, error })
            throw error
        }
    }

    /**
     * 批量插入数据
     */
    async insertBatch(table: string, dataArray: Record<string, any>[]): Promise<any> {
        try {
            if (dataArray.length === 0) return []

            const columns = Object.keys(dataArray[0])
            let batchValues = ''

            for (const data of dataArray) {
                const values = columns.map(col => this.formatSqlValue(data[col]))
                batchValues += `(${values.join(',')}),`
            }
            batchValues = batchValues.replace(/,*$/, '')

            const batchSql = `INSERT INTO ${table} (${columns.join(',')}) VALUES ${batchValues}`
            console.log('批量插入数据 ---insertBatch SQL:>>', batchSql)

            const request = this.getPool().request()
            const result = await request.query(batchSql)
            return result
        } catch (error) {
            logError('ERROR', 'CXT-DB', '批量插入数据失败', { table, count: dataArray.length, error })
            throw error
        }
    }

    /**
     * 更新数据
     */
    async update(table: string, where: Record<string, any>, data: Record<string, any>, fieldSet: Record<string, string> = {}): Promise<any> {

        const updateSQL = async () => {
            try {
                console.log('更新数据UPDATE---table,where,data:>>', table, where, data)
                const updateFields = Object.keys(data).map(key => `${key} = ${this.formatSqlValue(data[key])}`).join(',')
                let whereClause = ''

                // 添加查询条件
                // for (const [key, value] of Object.entries(where)) {
                //     if (key && value !== undefined && value !== null) {
                //         whereClause += ` ${key} = ${this.formatSqlValue(value)} AND`
                //     }
                // }
                for (const whereKey in where) {
                if (Object.prototype.toString.call(where[whereKey]) === '[object Object]') {
                    if (where[whereKey].type === 'in') {
                    whereClause += ` ${whereKey} ${where[whereKey].type} ${where[whereKey].value} AND`
                    } else {
                    whereClause += ` ${whereKey} ${where[whereKey].type} '${where[whereKey].value}' AND`
                    }
                } else {
                    whereClause += ` ${whereKey}='${where[whereKey]}' AND`
                }
                }

                if (whereClause) {
                    whereClause = 'WHERE' + whereClause.replace(/AND$/, '')
                }

                const sql = `UPDATE ${table} SET ${updateFields} ${whereClause}`
                console.log('更新数据--update SQL:>>', sql)
                const request = this.getPool().request()
                const result = await request.query(sql)
                return result
            } catch (error) {
                logError('ERROR', 'CXT-DB', '更新数据失败', { table, where, error })
                throw error
            }
        }
        try {
            return await updateSQL()
        } catch (error) {
            return await updateSQL()
        }

    }

    // async update(table: string, where: Record<string, any>, data: Record<string, any>, fieldSet: Record<string, string> = {}): Promise<any> {
    //     console.log('UPDATE---table:>>', table)
    //     console.log('UPDATE---data:>>', data)
    //     console.log('UPDATE---fieldSet:>>', fieldSet)
    //     console.log('UPDATE---where:>>', where)
    //     let set = ''
    //     for (const fieldSetKey in fieldSet) {
    //         set = set + `${fieldSetKey}=${data[fieldSetKey]},`
    //     }
    //     for (const dataKey in data) {
    //         set = set + `${dataKey}='${data[dataKey]}',`
    //     }
    //     if (set) {
    //         set = set.replace(/,*$/, '')
    //     }

    //     let whereClause = ''
    //     for (const whereKey in where) {
    //         // UPDATE OrderZhuoTaiDish SET where='[object Object]',data='[object Object]' WHERE id='undefined' 
    //         if (Object.prototype.toString.call(where[whereKey]) === '[object Object]') {
    //             if (where[whereKey].type === 'in') {
    //                 whereClause += ` ${whereKey} ${where[whereKey].type} ${where[whereKey].value} AND`
    //             } else {
    //                 whereClause += ` ${whereKey} ${where[whereKey].type} '${where[whereKey].value}' AND`
    //             }
    //         } else {
    //             whereClause += ` ${whereKey}='${where[whereKey]}' AND`
    //         }
    //     }
    //     if (whereClause) {
    //         whereClause = 'WHERE' + whereClause.replace(/AND*$/, '')
    //     }
    //     const request = this.getPool().request()
    //     const sql = `UPDATE ${table} SET ${set} ${whereClause}`
    //     console.log('UPDATE---sql:>>', sql)
    //     const result = await request.query(sql)
    //     return result
    // }

    /**
     * 批量更新数据
     */
    async updateBatch(table: string, dataArray: Record<string, any>[]): Promise<any> {
        console.log('批量更新数据---table,dataArray>', table, dataArray)
        // 处理批量更新逻辑
        const results = []
        for (const item of dataArray) {
            // 检查数据结构：如果是 { where: {...}, data: {...} } 格式
            if (item.where && item.data) {
                const result = await this.update(table, item.where, item.data)
                results.push(result)
            } else {
                // 兼容旧格式：{ id: xxx, field1: value1, field2: value2 }
                const { id, ...updateData } = item
                const result = await this.update(table, { id }, updateData)
                results.push(result)
            }
        }
        return results
    }

    /**
     * 删除数据
     */
    async delete(table: string, where: Record<string, any>): Promise<any> {
        try {
            let whereClause = ''

            for (const [key, value] of Object.entries(where)) {
                if (key && value !== undefined && value !== null) {
                    whereClause += ` ${key} = ${this.formatSqlValue(value)} AND`
                }
            }

            if (whereClause) {
                whereClause = 'WHERE' + whereClause.replace(/AND$/, '')
            }

            const sql = `DELETE FROM ${table} ${whereClause}`
            console.log('delete SQL:>>', sql)

            const request = this.getPool().request()
            const result = await request.query(sql)
            return result
        } catch (error) {
            logError('ERROR', 'CXT-DB', '删除数据失败', { table, where, error })
            throw error
        }
    }

    /**
     * 执行自定义 SQL
     */
    async executeSQL(sql: string, params: Record<string, any> = {}): Promise<any> {
        try {
            // 如果传入了参数，替换SQL中的占位符
            let finalSql = sql
            for (const [key, value] of Object.entries(params)) {
                if (key && value !== undefined && value !== null) {
                    const placeholder = `@${key}`
                    const formattedValue = this.formatSqlValue(value)
                    finalSql = finalSql.replace(new RegExp(placeholder, 'g'), formattedValue)
                }
            }

            console.log('executeSQL:>>', finalSql)

            const request = this.getPool().request()
            const result = await request.query(finalSql)
            return result
        } catch (error) {
            logError('ERROR', 'CXT-DB', '执行SQL失败', { sql, params, error })
            throw error
        }
    }
}

// 导出单例实例
export const cxtDB = new CXTDatabase()

